"""
数据库初始化脚本
作者：资深Flask讲师
功能：创建数据库表并初始化测试数据
使用方法：python init_db.py
"""

from myflask.main import app
from myflask.tools.bd import db
from myflask.models.user import User
from myflask.models.department import Department
from myflask.models.leave_request import LeaveRequest
from datetime import datetime, date, timedelta


def init_database():
    """初始化数据库"""
    with app.app_context():
        print("=" * 60)
        print("开始初始化数据库...")
        print("=" * 60)
        
        # 1. 删除所有表（慎用！）
        print("\n[1/4] 删除旧表...")
        try:
            # 先禁用外键检查
            db.session.execute(db.text('SET FOREIGN_KEY_CHECKS = 0'))
            # 删除可能存在的遗留表
            db.session.execute(db.text('DROP TABLE IF EXISTS user_registrations'))
            # 删除所有已知的表
            db.drop_all()
            # 恢复外键检查
            db.session.execute(db.text('SET FOREIGN_KEY_CHECKS = 1'))
            db.session.commit()
            print("[OK] 删除旧表成功")
        except Exception as e:
            print(f"[警告] 删除表时出现错误: {e}")
            db.session.rollback()
        
        # 2. 创建所有表
        print("\n[2/4] 创建新表...")
        db.create_all()
        print("[OK] 创建新表成功")
        
        # 3. 初始化部门数据
        print("\n[3/4] 初始化部门数据...")
        departments_data = [
            {"name": "技术部", "description": "负责公司技术研发和产品开发"},
            {"name": "市场部", "description": "负责市场营销和品牌推广"},
            {"name": "人事部", "description": "负责人力资源管理和招聘"},
            {"name": "财务部", "description": "负责财务管理和会计核算"},
        ]
        
        departments = []
        for dept_data in departments_data:
            dept = Department(
                name=dept_data["name"],
                description=dept_data["description"],
                status=1
            )
            db.session.add(dept)
            departments.append(dept)
        
        db.session.commit()
        print(f"[OK] 成功创建 {len(departments)} 个部门")
        
        # 4. 初始化用户数据
        print("\n[4/4] 初始化用户数据...")
        
        # 4.1 创建超级管理员
        admin = User(
            user='admin',
            real_name='超级管理员',
            email='admin@example.com',
            phone='13800000000',
            role='admin',  # 超级管理员
            department_id=None,  # 管理员不属于任何部门
            status=1
        )
        admin.set_password('123456')
        db.session.add(admin)
        print("  [OK] 创建超级管理员: admin / 123456")
        
        # 4.2 创建部门经理（技术部）
        tech_manager = User(
            user='tech_manager',
            real_name='张三',
            email='zhangsan@example.com',
            phone='13800000001',
            role='manager',  # 部门经理
            department_id=departments[0].id,  # 技术部
            status=1
        )
        tech_manager.set_password('123456')
        db.session.add(tech_manager)
        departments[0].manager_id = tech_manager.id
        print("  [OK] 创建技术部经理: tech_manager / 123456")
        
        # 4.3 创建部门经理（市场部）
        market_manager = User(
            user='market_manager',
            real_name='李四',
            email='lisi@example.com',
            phone='13800000002',
            role='manager',  # 部门经理
            department_id=departments[1].id,  # 市场部
            status=1
        )
        market_manager.set_password('123456')
        db.session.add(market_manager)
        departments[1].manager_id = market_manager.id
        print("  [OK] 创建市场部经理: market_manager / 123456")
        
        # 4.4 创建普通员工（技术部）
        employees_data = [
            {"user": "tech_emp1", "real_name": "王五", "dept_id": departments[0].id, "dept_name": "技术部"},
            {"user": "tech_emp2", "real_name": "赵六", "dept_id": departments[0].id, "dept_name": "技术部"},
            {"user": "market_emp1", "real_name": "孙七", "dept_id": departments[1].id, "dept_name": "市场部"},
            {"user": "market_emp2", "real_name": "周八", "dept_id": departments[1].id, "dept_name": "市场部"},
            {"user": "hr_emp1", "real_name": "吴九", "dept_id": departments[2].id, "dept_name": "人事部"},
        ]
        
        for emp_data in employees_data:
            emp = User(
                user=emp_data["user"],
                real_name=emp_data["real_name"],
                email=f"{emp_data['user']}@example.com",
                phone=f"138{emp_data['user'][-4:].zfill(8)}",
                role='employee',  # 普通员工
                department_id=emp_data["dept_id"],
                status=1
            )
            emp.set_password('123456')
            db.session.add(emp)
            print(f"  [OK] 创建{emp_data['dept_name']}员工: {emp_data['user']} / 123456")
        
        db.session.commit()
        
        # 5. 初始化请假申请测试数据
        print("\n[5/6] 初始化请假申请数据...")
        
        # 获取已创建的用户
        admin = User.query.filter_by(user='admin').first()
        tech_manager = User.query.filter_by(user='tech_manager').first()
        market_manager = User.query.filter_by(user='market_manager').first()
        tech_emp1 = User.query.filter_by(user='tech_emp1').first()
        tech_emp2 = User.query.filter_by(user='tech_emp2').first()
        market_emp1 = User.query.filter_by(user='market_emp1').first()
        hr_emp1 = User.query.filter_by(user='hr_emp1').first()
        
        leave_requests_data = [
            # 待审批的请假申请
            {
                "user": tech_emp1,
                "leave_type": "sick",
                "start_date": date.today() + timedelta(days=1),
                "end_date": date.today() + timedelta(days=2),
                "days": 2,
                "reason": "感冒发烧，需要休息治疗",
                "status": "pending"
            },
            {
                "user": market_emp1,
                "leave_type": "personal",
                "start_date": date.today() + timedelta(days=3),
                "end_date": date.today() + timedelta(days=3),
                "days": 1,
                "reason": "家里有事需要处理",
                "status": "pending"
            },
            
            # 已通过的请假申请
            {
                "user": tech_emp2,
                "leave_type": "annual",
                "start_date": date.today() - timedelta(days=5),
                "end_date": date.today() - timedelta(days=3),
                "days": 3,
                "reason": "年假旅游",
                "status": "approved",
                "approver": tech_manager,
                "approved_at": datetime.now() - timedelta(days=6),
                "approval_comment": "同意，注意安全"
            },
            {
                "user": market_emp1,
                "leave_type": "sick",
                "start_date": date.today() - timedelta(days=10),
                "end_date": date.today() - timedelta(days=9),
                "days": 2,
                "reason": "身体不适，需要就医",
                "status": "approved",
                "approver": market_manager,
                "approved_at": datetime.now() - timedelta(days=11),
                "approval_comment": "批准，早日康复"
            },
            
            # 已拒绝的请假申请
            {
                "user": tech_emp1,
                "leave_type": "personal",
                "start_date": date.today() - timedelta(days=15),
                "end_date": date.today() - timedelta(days=14),
                "days": 2,
                "reason": "个人事务",
                "status": "rejected",
                "approver": tech_manager,
                "approved_at": datetime.now() - timedelta(days=16),
                "approval_comment": "项目紧急期间，暂不批准"
            },
            
            # 更多测试数据
            {
                "user": hr_emp1,
                "leave_type": "annual",
                "start_date": date.today() + timedelta(days=10),
                "end_date": date.today() + timedelta(days=12),
                "days": 3,
                "reason": "年假计划，回老家探亲",
                "status": "pending"
            },
            {
                "user": tech_emp2,
                "leave_type": "sick",
                "start_date": date.today() - timedelta(days=20),
                "end_date": date.today() - timedelta(days=20),
                "days": 1,
                "reason": "牙科治疗",
                "status": "approved",
                "approver": tech_manager,
                "approved_at": datetime.now() - timedelta(days=21),
                "approval_comment": "同意"
            },
        ]
        
        for leave_data in leave_requests_data:
            leave = LeaveRequest(
                user_id=leave_data["user"].id,
                department_id=leave_data["user"].department_id,
                leave_type=leave_data["leave_type"],
                start_date=leave_data["start_date"],
                end_date=leave_data["end_date"],
                days=leave_data["days"],
                reason=leave_data["reason"],
                status=leave_data["status"],
                approver_id=leave_data.get("approver").id if leave_data.get("approver") else None,
                approved_at=leave_data.get("approved_at"),
                approval_comment=leave_data.get("approval_comment")
            )
            db.session.add(leave)
        
        db.session.commit()
        print(f"[OK] 成功创建 {len(leave_requests_data)} 条请假记录")
        
        # 6. 显示统计信息
        print("\n" + "=" * 60)
        print("数据库初始化完成！")
        print("=" * 60)
        print(f"\n[统计信息]")
        print(f"  - 部门总数: {Department.query.count()}")
        print(f"  - 用户总数: {User.query.count()}")
        print(f"    * 超级管理员: {User.query.filter_by(role='admin').count()}")
        print(f"    * 部门经理: {User.query.filter_by(role='manager').count()}")
        print(f"    * 普通员工: {User.query.filter_by(role='employee').count()}")
        print(f"  - 请假申请总数: {LeaveRequest.query.count()}")
        print(f"    * 待审批: {LeaveRequest.query.filter_by(status='pending').count()}")
        print(f"    * 已通过: {LeaveRequest.query.filter_by(status='approved').count()}")
        print(f"    * 已拒绝: {LeaveRequest.query.filter_by(status='rejected').count()}")
        
        print(f"\n[测试账号]")
        print(f"  1. 超级管理员: admin / 123456")
        print(f"  2. 技术部经理: tech_manager / 123456")
        print(f"  3. 市场部经理: market_manager / 123456")
        print(f"  4. 技术部员工: tech_emp1 / 123456")
        print(f"  5. 市场部员工: market_emp1 / 123456")
        
        print(f"\n[提示]")
        print(f"  - 超级管理员可以访问所有接口和数据")
        print(f"  - 部门经理可以管理本部门员工和审批请假")
        print(f"  - 普通员工只能查看和编辑自己的数据")
        print(f"\n[请假系统测试]")
        print(f"  1. 访问 http://localhost:5173/leave 查看请假管理页面")
        print(f"  2. 员工可以提交请假申请")
        print(f"  3. 部门经理可以审批本部门请假")
        print(f"  4. 查看个人和部门请假统计")
        
        print("\n" + "=" * 60)


if __name__ == '__main__':
    init_database()

